package com.bookmngsys.dao;

import com.bookmngsys.entity.Book;
import com.bookmngsys.entity.BookInfo;
import com.bookmngsys.entity.BorrowBook;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;

public interface BookInfoDao extends JpaRepository<BookInfo, String> {
    @Query(value = "select * from book_info where book_id = :bookId", nativeQuery = true)
    BookInfo findBookByBookId(int bookId);

    @Query(value = "select * from book_info where isbn = :isbn", nativeQuery = true)
    ArrayList<BookInfo> findBookByIsbn(String isbn);


    @Query(value = "select * from book_info LIMIT :curPage,:pageSize", nativeQuery = true)
    ArrayList<BookInfo> findBookInfos(int curPage, int pageSize);
//    @Transactional
//    @Query(value = "select a.*,b.book_name,b.author,b.publisher,b.img_url from bookinfo a,book b where a.isbn=b.isbn and a.reader_id=:readerId and a.status=1", nativeQuery = true)
//    ArrayList<BorrowBook> getBorrowBooks(int readerId);

    @Query(value = "select * from book_info where isbn = :isbn and borrow_reader_id is null and (reserve_reader_id is null or :curDate > reserve_date + 1000 * 3600 * 4)", nativeQuery = true)
    ArrayList<BookInfo> findCanReservationByIsbn(String isbn, Long curDate);

    @Query(value = "select * from book_info where isbn = :isbn and reserve_reader_id = :readerId and reserve_date is not null and reserve_date is not null and reserve_date + 14400 * 1000 >= :curDate and borrow_reader_id is null", nativeQuery = true)
    BookInfo findReservationByIsbnAndReaderId(String isbn, int readerId, long curDate);

    @Query(value = "select * from book_info where book_id = :bookId and reserve_reader_id is not null and reserve_date is not null and reserve_date + 14400 * 1000 >= :curDate", nativeQuery = true)
    BookInfo findReservationByBookId(int bookId, long curDate);

    @Query(value = "select * from book_info where reserve_reader_id = :readerId", nativeQuery = true)
    ArrayList<BookInfo> findAllReservationByReaderId(int readerId);

    @Query(value = "select * from book_info where isbn = :isbn and borrow_reader_id = :readerId", nativeQuery = true)
    BookInfo findBorrowBookByIsbnAndReaderId(String isbn, int readerId);

    @Query(value = "select * from book_info where isbn = :isbn and borrow_reader_id is not null", nativeQuery = true)
    ArrayList<BookInfo> findBorrowBookByIsbn(String isbn);

    @Query(value = "select * from book_info where borrow_reader_id = :readerId", nativeQuery = true)
    ArrayList<BookInfo> findBorrowBookByReaderId(int readerId);


    @Query(value = "select * from book_info where book_id = :bookId", nativeQuery = true)
    BookInfo findBorrowBookByBookId(int bookId);

    @Query(value = "select count(*) from book_info where borrow_reader_id = :readerId", nativeQuery = true)
    Integer getBorrowNumByReaderId(int readerId);

    @Query(value = "select * from book_info where borrow_reader_id = :readerId", nativeQuery = true)
    ArrayList<BookInfo> getBorrowBookByReaderId(int readerId);

    @Query(value = "select count(*) from book_info where reserve_reader_id = :readerId and :curDate <= reserve_date + 14400 * 1000 ", nativeQuery = true)
    Integer getReservationNumByReaderId(int readerId, long curDate);


    @Transactional
    @Modifying
    @Query(value = "update from book_info set reserve_reader_id is null where book_id = :bookId", nativeQuery = true)
    void setNoReservation(int bookId);

    @Transactional
    @Modifying
    @Query(value = "update from book_info set borrow_reader_id is null where book_id = :bookId", nativeQuery = true)
    void setNoBorrow(int bookId);


    @Transactional
    @Modifying
    @Query(value = "update from book_info set borrow_reader_id is null,reserve_reader_id is null where book_id = :bookId", nativeQuery = true)
    void setNoBorrowAndNoReserveation(int bookId);

    @Transactional
    @Modifying
    @Query(value = "update from book_info set borrow_reader_id = :readerId,reserve_reader_id is null, borrow_date = :borrowDate, return_date = :returnDate  where book_id = :bookId", nativeQuery = true)
    void setBorrow(int bookId, int readerId, long borrowDate, long returnDate);


    @Transactional
    @Modifying
    @Query(value = "update book_info set damage = :status where book_id = :bookId", nativeQuery = true)
    void setDamage(int bookId,int status);

    @Transactional
    @Modifying
    @Query(value = "update book_info set lost = :status where book_id = :bookId", nativeQuery = true)
    void setLost(int bookId,int status);

}
